﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using EntitiesLayer.Entities;
using System.Data;


namespace DataLayer.DAO
{
    public class EmpleadoDAO
    {
        private Connection.ConnectionClinica cn;
        private OracleConnection objCn;

        public EmpleadoDAO()
        {
            cn = new Connection.ConnectionClinica();
            objCn = cn.Conecta();
        }

        public List<Empleado> consultarEmpleados(string apellido, string nombre)
        {
            List<Empleado> list = new List<Empleado>();

            using (OracleCommand cmd = new OracleCommand("CONSULTAREMPLEADOCRITERIO", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter refParameter = new OracleParameter();
                refParameter.ParameterName = "RECORDSET";
                refParameter.OracleType = OracleType.Cursor;
                refParameter.Direction = ParameterDirection.Output;

                OracleParameter apellidoParameter = new OracleParameter();
                apellidoParameter.ParameterName = "REMP_APELLIDOS_EMP";
                apellidoParameter.OracleType = OracleType.NVarChar;
                apellidoParameter.Size = 100;
                apellidoParameter.Direction = ParameterDirection.Input;
                apellidoParameter.Value = apellido.Trim().ToLower();

                OracleParameter nombreParameter = new OracleParameter();
                nombreParameter.ParameterName = "REMP_NOMBRES_EMP";
                nombreParameter.OracleType = OracleType.NVarChar;
                nombreParameter.Size = 100;
                nombreParameter.Direction = ParameterDirection.Input;
                nombreParameter.Value = nombre.Trim().ToLower();

                cmd.Parameters.Add(refParameter);
                cmd.Parameters.Add(apellidoParameter);
                cmd.Parameters.Add(nombreParameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Empleado p = new Empleado();
                        p.Documento_emp = Convert.ToString(dr[0]);
                        p.Apellidos_emp = Convert.ToString(dr[1]);
                        p.Nombres_emp = Convert.ToString(dr[2]);
                        p.Tfijo_emp = Convert.ToString(dr[3]);
                        p.Tcel_emp = Convert.ToString(dr[4]);
                        p.Email_emp = Convert.ToString(dr[5]);  
                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }


        public int insertEmpleados(Empleado obj)
        {
            int resultado = 0;
            try
            {
                OracleCommand cmd = new OracleCommand("Insertempleado", objCn);
                objCn.Open();
                cmd.Connection = objCn;
                cmd.CommandTimeout = 60;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new OracleParameter("R_vemp_apellidos_emp", OracleType.NVarChar, 50, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VEMP_APELLIDOS_EMP));
                cmd.Parameters.Add(new OracleParameter("R_vemp_nombres_emp", OracleType.NVarChar, 50, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VEMP_NOMBRES_EMP));
                cmd.Parameters.Add(new OracleParameter("R_vemp_nro_documento", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VEMP_NRO_DOCUMENTO));
                cmd.Parameters.Add(new OracleParameter("R_iemp_telef_fijo", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.IEMP_TELEF_FIJO));
                cmd.Parameters.Add(new OracleParameter("R_iemp_celular", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.IEMP_CELULAR));
                cmd.Parameters.Add(new OracleParameter("R_Vemp_email", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VEMP_EMAIL));
                cmd.Parameters.Add(new OracleParameter("R_vdoc_cod_tip_doc", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VDOC_COD_TIP_DOC));
                resultado = cmd.ExecuteNonQuery();
                
            }
            catch (Exception ex)
            {
                objCn.Dispose();
            }
            finally
            {
                if (objCn != null)
                {
                    objCn.Close();
                }
            }
            return resultado;
        }

        public int UpdateEmpleados(Empleado obj)
        {
            int resultado = 0;
            try
            {
                OracleCommand cmd = new OracleCommand("Updateempleado", objCn);
                objCn.Open();
                cmd.Connection = objCn;
                cmd.CommandTimeout = 60;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new OracleParameter("R_Vemp_Nro_Documento", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VEMP_NRO_DOCUMENTO));               
                cmd.Parameters.Add(new OracleParameter("R_Vemp_Apellidos_Emp", OracleType.NVarChar, 50, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VEMP_APELLIDOS_EMP));
                cmd.Parameters.Add(new OracleParameter("R_Vemp_Nombres_Emp", OracleType.NVarChar, 50, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VEMP_NOMBRES_EMP));
                cmd.Parameters.Add(new OracleParameter("R_Iemp_Telef_Fijo", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.IEMP_TELEF_FIJO));
                cmd.Parameters.Add(new OracleParameter("R_Iemp_Celular", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.IEMP_CELULAR));
                cmd.Parameters.Add(new OracleParameter("R_Vemp_Email", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VEMP_EMAIL));
                cmd.Parameters.Add(new OracleParameter("R_vdoc_cod_tip_doc", OracleType.NVarChar, 20, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, obj.VDOC_COD_TIP_DOC));                
                resultado = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                objCn.Dispose();
            }
            finally
            {
                if (objCn != null)
                {
                    objCn.Close();
                }
            }
            return resultado;
        }
 

       
    }
}
